const connection = require("../app/database");

class AnimalService {
  async getRandomAnimal(quantity) {
    const statement = "select * FROM animal ORDER BY RAND() LIMIT ?";
    const [result] = await connection.execute(statement, [quantity]);

    return result;
  }

  async getAnimalTypeService() {
    const statement = "SELECT * FROM animal_type";
    const [result] = await connection.execute(statement);

    return result;
  }

  async getAnimalsByType(typeId, limit, offset) {
    const animals = {
      animalLists: [],
      animalTotal: 0,
    };

    try {
      const statement1 =
        "SELECT COUNT(*) as total FROM animal WHERE animalType = ?";
      const [animalTotalResult] = await connection.execute(statement1, [
        typeId,
      ]);
      animals.animalTotal = animalTotalResult[0].total;

      if (!animalTotalResult[0].total) {
        return animals;
      }

      const statement2 =
        "SELECT * FROM animal WHERE animalType = ? LIMIT ? OFFSET ?;";
      const [animalListsResult] = await connection.execute(statement2, [
        typeId,
        limit,
        offset,
      ]);
      animals.animalLists = animalListsResult;

      return animals;
    } catch (error) {
      console.log(console.error);
    }
  }

  async likeSearchAnimal(keyWord, limit, offset) {
    const animals = {
      animalLists: [],
      animalTotal: 0,
    };

    try {
      const statement1 = `
    SELECT COUNT(*) as total FROM animal
    WHERE 
        name LIKE ? OR
        scientificName LIKE ? OR
        kingdom LIKE ? OR
        phylum LIKE ? OR
        class LIKE ? OR
        orderName LIKE ? OR
        family LIKE ? OR
        genus LIKE ? OR
        species LIKE ? OR
        endangeredLevel LIKE ? OR
        protectionLevel LIKE ?
    `;
      const params1 = Array(11).fill(`%${keyWord}%`);
      const [animalTotalResult] = await connection.execute(statement1, params1);
      animals.animalTotal = animalTotalResult[0].total;

      if (!animalTotalResult[0].total) {
        return animals;
      }

      const statement2 = `
    SELECT * FROM animal
    WHERE 
        name LIKE ? OR
        scientificName LIKE ? OR
        kingdom LIKE ? OR
        phylum LIKE ? OR
        class LIKE ? OR
        orderName LIKE ? OR
        family LIKE ? OR
        genus LIKE ? OR
        species LIKE ? OR
        endangeredLevel LIKE ? OR
        protectionLevel LIKE ?
        LIMIT ? OFFSET ?;
    `;
      // 这行代码的作用是创建一个长度为11的数组，并且每个元素都填充为 %关键词% 的字符串。
      const params2 = Array(11).fill(`%${keyWord}%`);
      params2.push(limit);
      params2.push(offset);
      console.log(params2);

      const [animalListsResult] = await connection.execute(statement2, params2);
      animals.animalLists = animalListsResult;

      return animals;
    } catch (error) {
      console.log(console.log(error));
    }
  }

  async getAnimalById(animalId) {
    const statement = "select * FROM animal WHERE id = ?";
    const [result] = await connection.execute(statement, [animalId]);

    return result[0];
  }
}

module.exports = new AnimalService();
